"""
Case Type   : 事务控制
Case Name   :  执行sql循环，匿名块提交后进行事务查询
Create At   : 2020/12/02
Owner       : openGauss1
Description :
    1.新建测试表
    2.将sql循环语句放在匿名块变量中执行，对数据库表进行数据写入
    3.查看主机数据
    4.查看备机数据
Expect      :
    1.创建测试表成功
    2.将sql循环语句放在匿名块变量中执行，对数据库表进行数据写入成功
    3.查看主机数据正确
    4.查看备机数据同步成功
History     :
    2024/9/3 modify by peilinqian: 优化整体用例，增加主备同步等待，确保备机数据同步
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

pri_sh = CommonSH('PrimaryDbUser')
node_num = pri_sh.get_node_num()


@unittest.skipIf(node_num < 3, '非1主2备环境不执行')
class TransactionFile(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info("----this is setup----")
        self.log.info(f'----{os.path.basename(__file__)}:start----')
        self.constant = Constant()
        self.sta1_sh = CommonSH('Standby1DbUser')
        self.tb_name = 't_dml_transaction_case0015'

    def test_transaction_file(self):
        step_txt = '----step1:新建测试表 expect:创建成功----'
        self.log.info(step_txt)
        create_sql = f"drop table if exists {self.tb_name};" \
                     f"create table {self.tb_name} (sk integer,id char(16)," \
                     f"name varchar(20),sq_ft integer);"
        create_result = pri_sh.execut_db_sql(create_sql)
        self.log.info(create_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_result,
                      '执行失败' + step_txt)

        step_txt = '----step2:使用匿名块对主机数据进行数据写入 expect:成功----'
        self.log.info(step_txt)
        sql_cmd = f''' declare 
                       i integer;
                       begin
                         i :=1;
                         forall i in 1..10
                         insert into {self.tb_name} values(i,'sk1','tt',3332);
                       end;
                       '''
        sql_result = pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_result)
        self.assertIn(self.constant.CREATE_ANONYMOUS_BLOCK_SUCCESS_MSG,
                      sql_result, '执行失败' + step_txt)

        step_txt = '----step3:查看主机数据 expect:正确----'
        self.log.info(step_txt)
        sql_cmd = f'''select * from {self.tb_name};'''
        pri_result = pri_sh.execut_db_sql(sql_cmd)
        self.log.info(pri_result)
        self.assertIn('10 rows', pri_result, '执行失败' + step_txt)

        self.log.info('----等待主备同步----')
        self.sta1_sh.check_location_consistency('standby', node_num)

        step_txt = '----step4:查看备机数据 expect:已同步正确----'
        self.log.info(step_txt)
        sta_result = self.sta1_sh.execut_db_sql(sql_cmd)
        self.log.info(sta_result)
        self.assertEqual(pri_result, sta_result, '执行失败' + step_txt)

    def tearDown(self):
        self.log.info('----this is teardown----')
        self.log.info('----清理环境----')
        step_txt = '----删除表; expect:删除成功----'
        self.log.info(step_txt)
        drop_sql = f"drop table if exists {self.tb_name};"
        drop_result = pri_sh.execut_db_sql(drop_sql)
        self.log.info(drop_result)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, drop_result,
                      '执行失败' + step_txt)

        self.log.info(f'----{os.path.basename(__file__)}:end----')
